/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package test;
import constants.Constants;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class Export2Office {
	//workbook ta làm việc ~ excel file
	private static Workbook wb;
        private static Connection connect =null;
	private static PreparedStatement pstm=null;
	public Export2Office(Workbook wb) {
		this.wb=wb;
	}
	/**
	 * Export 1 result set ra file excel
	 * @param rs là result set cần xuất
	 * @param sheetName là tên của sheet trong workbook
	 * @throws Exception
	 */
	public static void Export(ResultSet rs, String sheetName)throws Exception{
		int nos=wb.getNumberOfSheets();
		for (int i = 0; i < nos; i++) {
			String sn=wb.getSheetName(i);
			if(sn.equalsIgnoreCase(sheetName)){
				sheetName+="$1";
			}
		}
		//tạo worksheet
		Sheet sheet = wb.createSheet(sheetName);
		//Lấy metadata làm column header
		ResultSetMetaData rsmd=rs.getMetaData();
		//tạo dòng tiêu đề
		Row row = sheet.createRow((short)0);
		int colNum=rsmd.getColumnCount();//số fields có trong bảng
		//duyệt qua các fields lấy làm column header
		for (int i = 0; i < colNum; i++) {
			String col=rsmd.getColumnName(i+1);
			Cell cell=row.createCell(i);
			cell.setCellValue(col);
		}
		//điền giá trị vào các hàng
		while(rs.next()){
			//tạo 1 hàng mới
			row=sheet.createRow(rs.getRow());
			for (int i = 0; i < colNum; i++) {
				String val=rs.getString(i+1);
				//tạo 1 ô mới
				Cell cell=row.createCell(i);
				cell.setCellValue(val);
			}
		}

	}
        public static ResultSet getAll(String table){
                connect= dao.Connector.getConnect();
		ResultSet rs=null;
		try{
			pstm= connect.prepareStatement("Select *from "+ table+" where id=7");
			rs=pstm.executeQuery();
			
		}catch(SQLException e){
			e.printStackTrace();
		}
                return rs;
		
        }
	public static void ExportTable(String tableName,String excelFilePath) throws Exception{
		connect = dao.Connector.getConnect();
		ResultSet rs= getAll(tableName);
		Export(rs,tableName);
		//Ghi dữ liệu xuống file
		String fileName=excelFilePath;
                try (FileOutputStream fos = new FileOutputStream(fileName,true)) {
                    try {
                        wb.write(fos);
                    } catch (IOException ex) {
                        JOptionPane.showMessageDialog(null,"File"+fileName+" đã tồn tại");
                    }
                }
	}
        public static void export() throws Exception {
           wb=new HSSFWorkbook();
           ArrayList<entity.Customer> list = null;
                                
        }
        public static void ExportTest(ArrayList<entity.Customer> list,String sheetName, String fileName)throws Exception{
		wb=new HSSFWorkbook();
                int nos=wb.getNumberOfSheets();
		for (int i = 0; i < nos; i++) {
			String sn=wb.getSheetName(i);
			if(sn.equalsIgnoreCase(sheetName)){
				sheetName+="$1";
                                System.out.println(sheetName);
			}
		}
		//tạo worksheet
		Sheet sheet = wb.createSheet(sheetName);
		Row row = sheet.createRow((short)0);
		for (int i = 0; i < 8; i++) {
			Cell cell=row.createCell(i);
			cell.setCellValue("test");
		}
                 String[] name ={"Mã khách hàng","Họ tên","Tuổi","Giới tính","CMND","Địa chỉ","Số điện thoại","Quốc tịch"};
          
                for(int j=0;j<list.size();j++){	
                        Cell cell;
			row=sheet.createRow(j);
                       
                       for (int i = 0; i < 8; i++) {
                            if(j==0){
                            cell=row.createCell(i);
                             cell.setCellValue(name[i]);
                            }else
                         switch(i){  
                            case 0: 
                                cell=row.createCell(i);
				cell.setCellValue(list.get(j).getId());break;
                            case 1: 
                                cell=row.createCell(i);
				cell.setCellValue(list.get(j).getName());break;
                            case 2: 
                                cell=row.createCell(i);
				cell.setCellValue(list.get(j).getAge());break;
                            case 3: 
                                cell=row.createCell(i);
				cell.setCellValue(Constants.GENDER[list.get(j-1).getGender()]);break;
                            case 4:  
                                cell=row.createCell(i);
				cell.setCellValue(list.get(j).getId_card());break;
                            case 5:
                                cell=row.createCell(i);
				cell.setCellValue(list.get(j).getAddress());break;
                            case 6:
                                cell=row.createCell(i);
				cell.setCellValue( list.get(j).getPhone());break;                                
                            case 7: 
                                cell=row.createCell(i);
				cell.setCellValue(list.get(j-1).getCountry());break;                                 
                            }
				
			}
		}
                try (FileOutputStream fos = new FileOutputStream(fileName,true)) {
                    try {
                        wb.write(fos);
                    } catch (IOException ex) {
                        JOptionPane.showMessageDialog(null,"File"+fileName+" đã tồn tại");
                    }
                }

	}
}
